USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_MONTO_LIQUIDO]    Script Date: 11/24/2011 14:33:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0002_MONTO_LIQUIDO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0002_MONTO_LIQUIDO]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_MONTO_LIQUIDO]    Script Date: 11/24/2011 14:33:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0002_MONTO_LIQUIDO] 
AS 
BEGIN 
	/*select XllAosuc as suc , XllAocta as cta , XllAooper as oper , XLLCAPITAL as monto_liquido
	into #monto_liq
	from X054023 (NOLOCK),
			FSD010 (NOLOCK)
	where XllAosuc = aosuc AND XllAocta = aocta AND XllAooper = aooper AND
			 aosbop = 0 AND aocta <> 999999999 AND aooper <> 999999999 AND
			  aosbop = XllAosbop AND XllAotop = Aotope AND aomod = XllAomod
	order by XllFValor

	select cre.aosuc, cre.aocta,
		   cre.aooper, cre.aoimp,
		   val_seg = 
		   CASE 
			 WHEN segdegr.val_seg is null THEN 0
			 ELSE segdegr.val_seg
			END ,
		   seguroVida = 
		   CASE 
			 WHEN sevvid.val_seg is null THEN 0
			 ELSE sevvid.val_seg
			END,

		   seguroAsisten = 
		   CASE 
			 WHEN segasis.val_seg is null THEN 0
			 ELSE segasis.val_seg
			END,
	       
		   notario = 
		   CASE 
			 WHEN nota.notario is null THEN 0
			 ELSE nota.notario
			END,
	       
	       
		   impuesto = 
		   CASE 
			 WHEN impu.impuesto is null THEN 0
			 ELSE impu.impuesto
			END
	       
	into #monto_liqui
	from fsd010 as cre (nolock),
		 seguro_degr as segdegr,
		 seguro_vida_tra as sevvid,
		 seguro_asisten_pen as segasis,
		 notaria as nota,
		 impuesto as impu
	where  cre.aosbop  = 0 AND
		   cre.aotasa <> 0 AND 
		   cre.aocta  *= segdegr.ppcta AND
		   cre.aooper *= segdegr.ppoper AND
		   cre.aosuc  *= segdegr.ppsuc AND
		   cre.aocta  *= sevvid.cta AND
		   cre.aooper *= sevvid.ope AND
		   cre.aosuc  *= sevvid.suc AND
		   cre.aocta  *= segasis.ppcta AND
		   cre.aooper *= segasis.ppoper AND
		   cre.aosuc  *= segasis.ppsuc AND
		   cre.aocta *= nota.cta AND
		   cre.aooper *=  nota.ope AND
		   cre.aosuc  *=  nota.suc AND
		   cre.aocta *=   impu.cta AND
		   cre.aooper *=  impu.ope AND
		   cre.aosuc  *=  impu.suc AND 
		   not exists (select * from #monto_liq where cre.aooper = oper AND cre.aosuc = suc AND cre.aocta = cta ) AND
		   not exists (SELECT * FROM as_desistidos as anu WHERE cre.aocta = anu.hcta AND cre.aooper = anu.hoper AND cre.aosuc = anu.hsucur)
	  
	select * from #monto_liqui


	select aosuc, aocta, aooper, aoimp - (val_seg + seguroVida + seguroAsisten + notario + impuesto ) as MTOLIQ  
	into dbo.monto_liq
	from #monto_liqui
	union 
	select * from #monto_liq
	*/

	SELECT	XllAosuc AS aosuc
			,XllAocta AS aocta
			,XllAooper AS aooper
			,XLLCAPITAL AS MTOLIQ
	INTO	dbo.monto_liq
	FROM	X054023 (NOLOCK)
			,FSD010 (NOLOCK)
	WHERE	XllAosuc = aosuc
	AND		XllAocta = aocta 
	AND		XllAooper = aooper
	AND		aosbop = 0 
	AND		aocta <> 999999999 
	AND		aooper <> 999999999 
	AND		aosbop = XllAosbop 
	AND		XllAotop = Aotope 
	AND		aomod = XllAomod
	ORDER BY XllFValor
END

GO


